package com.mgy.common.excel;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.nio.charset.Charset;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * excel导出，支持大量数据导出
 *
 * @author maguoyong
 * @since 2018/6/13
 */
public class ExcelWriter {

    private SXSSFWorkbook workbook;

    /**
     * 默认第一个工作簿名称
     */
    private static final String DEFAULT_SHEET_NAME = "Sheet1";
    /**
     * 行映射
     */
    private Map<String, Integer> rowMap = new HashMap<>();

    /**
     * 每列列宽
     */
    private int[] arrColWidth = null;
    /**
     * 时间样式
     */
    private CellStyle dateStyle;
    /**
     * 列头样式
     */
    private CellStyle columnStyle;
    /**
     * 列头字体样式
     */
    private Font columnFont;
    /**
     * 行内容样式
     */
    private CellStyle contentStyle;
    /**
     * 行内容字体样式
     */
    private Font contentFont;
    /**
     * 自定义数据表
     */
    private DataTable dataTable;

    private Map<ExcelStyle, CellStyle> rowStyleMap = new HashMap<>();
    private Map<ExcelStyle, Font> rowFontMap = new HashMap<>();


    public ExcelWriter(int rowAccessWindowSize, String templatePath) throws Exception {
        File file = new File(templatePath);
        if (!file.exists() || !file.isFile()) {
            throw new Exception("文件不存在：" + templatePath);
        }
        //输入模板文件
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream(templatePath));
        this.workbook = new SXSSFWorkbook(xssfWorkbook, rowAccessWindowSize);
        //时间样式
        this.dateStyle = workbook.createCellStyle();
        DataFormat format = workbook.createDataFormat();
        dateStyle.setDataFormat(format.getFormat("yyyy-MM-dd HH:mm:ss"));

        //列头样式
        this.columnStyle = workbook.createCellStyle();
        this.columnFont = workbook.createFont();

        //内容样式
        this.contentStyle = workbook.createCellStyle();
        this.contentFont = workbook.createFont();
    }

    public ExcelWriter(String templatePath) throws Exception {
        this(100, templatePath);
    }

    /**
     * 初始化数据
     *
     * @param dt 自定义数据表
     */
    private void init(DataTable dt) {
        this.dataTable = dt;
        //设置列头样式
        ExcelUtil.setCustomCellStyle(columnStyle, columnFont, dt.getColumnStyle());
        //设置内容样式
        ExcelUtil.setCustomCellStyle(contentStyle, contentFont, dt.getContentStyle());
    }

    /**
     * 生成excel工作簿
     *
     * @param isNewSheet true:新建工作簿sheet,此时sheetName不能为空,false:默认取第一个工作簿，如果不存在，则创建一个新工作簿
     * @param sheetName  工作簿的名称
     * @param dt         数据源
     * @throws Exception Exception
     */
    public void importSheet(boolean isNewSheet, String sheetName, DataTable dt) throws Exception {
        this.init(dt);
        Sheet sheet = this.getSheet(isNewSheet, sheetName);
        sheetName = sheet.getSheetName();
        Integer nextRowIndex = rowMap.get(sheetName);
        if (nextRowIndex == null) {
            nextRowIndex = 0;
            //设置表头
            if (dt.getHeader() != null && dt.getHeader().trim().length() > 0) {
                nextRowIndex = this.setTableHeader(nextRowIndex, sheet);
            }
            //设置列头
            nextRowIndex = this.setColumnHeader(nextRowIndex, dt.getColumns(), sheet);
            //设置行数据
            nextRowIndex = this.setExcelRow(nextRowIndex, dt.getRows(), sheet);
            rowMap.put(sheetName, nextRowIndex);
        } else {
            //设置行数据
            nextRowIndex = this.setExcelRow(nextRowIndex, dt.getRows(), sheet);
            rowMap.put(sheetName, nextRowIndex);
        }
        //设置最大列宽
        this.setMaxColumnWith(sheet, dt.getColumns());
        //合并单元格
        this.setMergedRegion(sheet, dt.getCellRanges());
        //清空行数据
        dt.getRows().clear();
    }

    /**
     * 设置表头
     *
     * @param nextRowIndex 行号
     * @param sheet        工作簿
     * @return 下一行的行号
     */
    private Integer setTableHeader(Integer nextRowIndex, Sheet sheet) {
        CellStyle headerStyle = workbook.createCellStyle();
        Font headerFont = workbook.createFont();
        ExcelUtil.setCustomCellStyle(headerStyle, headerFont, dataTable.getHeaderStyle());
        //设置列头
        Row excelRow = sheet.createRow(nextRowIndex);
        Cell headerCell = excelRow.createCell(0);
        headerCell.setCellValue(dataTable.getHeader());
        headerCell.setCellStyle(headerStyle);
        //设置行高
        if (dataTable.getHeaderStyle().getHeight() != null) {
            excelRow.setHeight(dataTable.getHeaderStyle().getHeight());
        }
        sheet.addMergedRegion(new CellRangeAddress(nextRowIndex, nextRowIndex, 0, dataTable.getColumns().size() - 1));
        nextRowIndex++;
        return nextRowIndex;
    }

    /**
     * 设置列头
     *
     * @param nextRowIndex 下一行索引号
     * @param columns      列头数据
     * @param sheet        工作簿
     * @return 下一行索引号
     */
    private Integer setColumnHeader(Integer nextRowIndex, DataColumnCollection columns, Sheet sheet) {
        arrColWidth = new int[columns.size()];
        //设置列头
        SXSSFRow excelRow = (SXSSFRow) sheet.createRow(nextRowIndex);
        //设置行高
        if (dataTable.getColumnStyle().getHeight() != null) {
            excelRow.setHeight(dataTable.getColumnStyle().getHeight());
        }
        for (DataColumn column : columns) {
            arrColWidth[column.getOrdinal()] = column.getColumnName().getBytes(Charset.forName("UTF-8")).length;
            SXSSFCell cell = excelRow.createCell(column.getOrdinal());
            cell.setCellStyle(columnStyle);
            cell.setCellValue(column.getColumnName());
        }
        nextRowIndex++;
        return nextRowIndex;
    }

    /**
     * 设置行数据
     *
     * @param nextRowIndex 下一行的索引号
     * @param rows         数据行
     * @param sheet        工作簿
     * @return 下一行的索引号
     */
    private Integer setExcelRow(Integer nextRowIndex, DataRowCollection rows, Sheet sheet) {
        for (DataRow row : rows) {
            SXSSFRow excelRow = (SXSSFRow) sheet.createRow(nextRowIndex);
            //设置行高
            if (row.getRowStyle() != null && row.getRowStyle().getHeight() != null) {
                excelRow.setHeight(row.getRowStyle().getHeight());
            } else if (dataTable.getContentStyle().getHeight() != null) {
                excelRow.setHeight(dataTable.getContentStyle().getHeight());
            }
            //合并行
            if (row.getMerge()) {
                StringBuilder stringBuilder = new StringBuilder();
                for (DataColumn column : row.getColumns()) {
                    stringBuilder.append(row.getValue(column.getOrdinal()));
                }
                SXSSFCell cell = excelRow.createCell(0);
                cell.setCellValue(stringBuilder.toString());
                //设置内容样式
                cell.setCellStyle(row.getRowStyle() == null ? contentStyle : getRowStyle(row.getRowStyle()));
                sheet.addMergedRegion(new CellRangeAddress(nextRowIndex, nextRowIndex, 0, row.getColumns().size() - 1));
            } else {
                for (DataColumn column : row.getColumns()) {
                    SXSSFCell cell = excelRow.createCell(column.getOrdinal());
                    //设置内容样式
                    cell.setCellStyle(row.getRowStyle() == null ? contentStyle : getRowStyle(row.getRowStyle()));
                    Object cellValue = row.getValue(column.getOrdinal());
                    //不同列数据格式的转换
                    ExcelUtil.convertValueStyle(column.getDataType(), cellValue, cell, dateStyle);
                    //计算列宽
                    this.calculateColumnWith(row, column);

                }
            }
            nextRowIndex++;
        }
        return nextRowIndex;
    }

    /**
     * 合并单元格
     */
    private void setMergedRegion(Sheet sheet, List<CellRange> cellRanges) {
        for (CellRange cellRange : cellRanges) {
            sheet.addMergedRegion(new CellRangeAddress(cellRange.getFirstRow(), cellRange.getLastRow(), cellRange.getFirstColumn(), cellRange.getLastColumn()));
        }
    }

    /**
     * 计算每列最大列宽
     *
     * @param row    row
     * @param column column
     */
    private void calculateColumnWith(DataRow row, DataColumn column) {
        //计算最大的列宽
        int intTemp = (row.getValue(column.getOrdinal()) != null ? row.getValue(column.getOrdinal()) : "").toString().
                getBytes(Charset.forName("UTF-8")).length;
        if (intTemp > arrColWidth[column.getOrdinal()]) {
            arrColWidth[column.getOrdinal()] = intTemp;
        }
    }

    /**
     * 设置最大列宽
     */
    private void setMaxColumnWith(Sheet sheet, DataColumnCollection columns) {
        for (DataColumn column : columns) {
            //设置列宽
            if (arrColWidth[column.getOrdinal()] < 100) {
                sheet.setColumnWidth(column.getOrdinal(), (arrColWidth[column.getOrdinal()] + 1) * 256);
            } else {
                sheet.setColumnWidth(column.getOrdinal(), 100 * 256);
            }
        }
    }

    /**
     * 从模板中获取一个指定的工作簿
     *
     * @param isCreateSheet true:新建工作簿sheet,此时sheetName不能为空,false:默认取第一个工作簿，如果不存在，则创建一个新工作簿
     * @param sheetName     工作簿的名称
     * @return Sheet
     * @throws Exception Exception
     */
    private Sheet getSheet(boolean isCreateSheet, String sheetName) throws Exception {
        Sheet sheet;
        if (sheetName != null && sheetName.trim().length() > 0) {
            sheetName = sheetName.replaceAll("\\[", "").replaceAll("]", "");
        }
        if (isCreateSheet) {
            if (sheetName == null || sheetName.trim().length() == 0) {
                throw new Exception("sheetName不能为空");
            }
            sheet = workbook.getSheetAt(0);
            if (DEFAULT_SHEET_NAME.equals(sheet.getSheetName())) {
                workbook.setSheetName(0, sheetName);
            } else {
                sheet = workbook.createSheet(sheetName);
            }
        } else {
            sheet = workbook.getSheetAt(0);
            if (sheetName != null && sheetName.trim().length() > 0) {
                workbook.setSheetName(0, sheetName);
            }
        }
        return sheet;
    }


    /**
     * web导出excel，设置excel样式
     *
     * @param fileName 导出文件的文件名字
     * @param request  request
     * @param response response
     */
    private void exportExcel(String fileName, HttpServletRequest request, HttpServletResponse response) throws IOException {
        ExcelUtil.setOutputInfo(fileName, request, response);
        OutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.close();
    }

    /**
     * web导出excel，设置excel样式
     *
     * @param dtSource 要导出的数据源
     * @param fileName 导出文件的文件名字
     * @param request  request
     * @param response response
     * @throws Exception Exception
     */
    public void exportExcel(DataTable dtSource, String fileName, HttpServletRequest request, HttpServletResponse response) throws Exception {
        this.importSheet(false, null, dtSource);
        this.exportExcel(fileName, request, response);
    }

    /**
     * web导出excel，设置excel样式
     *
     * @param templatePath excel模板
     * @param dtSource     要导出的数据源
     * @param fileName     导出文件的文件名字
     * @param request      request
     * @param response     response
     * @throws Exception Exception
     */
    public static void exportExcel(String templatePath, DataTable dtSource, String fileName, HttpServletRequest request, HttpServletResponse response) throws Exception {
        ExcelWriter excelWriter = new ExcelWriter(templatePath);
        excelWriter.importSheet(false, null, dtSource);
        excelWriter.exportExcel(fileName, request, response);
    }


    /**
     * 保存到指定目录
     *
     * @param fileName      文件名称
     * @param saveDirectory 保存服务器目录
     * @throws Exception Exception
     */
    public void saveExcel(String fileName, String saveDirectory) throws Exception {
        String savePath = saveDirectory + File.separator + fileName;
        File file = new File(savePath);
        this.saveExcel(file);
    }

    /**
     * 保存到指定目录
     *
     * @param file 文件名称
     * @throws Exception Exception
     */
    public void saveExcel(File file) throws Exception {
        String dir = file.getParent();
        File dirFile = new File(dir);
        if (!dirFile.exists() || !dirFile.isDirectory()) {
            dirFile.mkdir();
        }

        OutputStream outputStream = new FileOutputStream(file);
        workbook.write(outputStream);
        outputStream.close();
    }

    private CellStyle getRowStyle(ExcelStyle rowStyle) {
        CellStyle cellStyle = rowStyleMap.get(rowStyle);
        if (cellStyle != null) {
            return cellStyle;
        }
        convertExcelStyle(rowStyle);
        return rowStyleMap.get(rowStyle);
    }

    private Font getRowFont(ExcelStyle rowStyle) {
        Font font = rowFontMap.get(rowStyle);
        if (font != null) {
            return font;
        }
        convertExcelStyle(rowStyle);
        return rowFontMap.get(rowStyle);
    }

    private void convertExcelStyle(ExcelStyle rowStyle) {
        CellStyle cellStyle = rowStyleMap.get(rowStyle);
        Font font = rowFontMap.get(rowStyle);
        if (cellStyle == null) {
            cellStyle = workbook.createCellStyle();
        }
        if (font == null) {
            font = workbook.createFont();
        }
        ExcelUtil.setCustomCellStyle(cellStyle, font, rowStyle);
        rowStyleMap.put(rowStyle, cellStyle);
        rowFontMap.put(rowStyle, font);
    }


}
